package com.stable.model;

import com.stable.spider.eastmoney.EastMoneySpider;
import com.stable.spider.ths.XLSData;
import com.stable.vo.BonusFinancing;
import com.stable.vo.EastBasic;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

//PE三阶段估值模型
public class PE3PB3 {

    public static void PE3PB3writeXls(String code, HSSFWorkbook workbook, List<XLSData> benefitList, List<XLSData> cashList, List<XLSData> debtList, List<XLSData> mainList) {
        EastMoneySpider eastMoneySpider = new EastMoneySpider();
        EastBasic basicInfo = eastMoneySpider.getBasicInfo(code);

        HSSFSheet sheet = workbook.createSheet("股东价值-一键估值|" + basicInfo.getName());// 创建工作表(Sheet)
        List nullList = Arrays.asList();

        HSSFUtil.row(workbook, sheet, 0, code + ":报告参数", "科目\\时间", benefitList);
        HSSFUtil.row(workbook, sheet, 1, "净利润:NI", "净利润(元)", mainList);

        HSSFUtil.rowFormula(workbook, sheet, 2, "净利润：亿", "CS2/100000000", benefitList, "0.00");

        HSSFRow row3 = sheet.createRow(3);
        HSSFUtil.rowColumn(workbook, row3, 13, basicInfo.getName()+"|"+basicInfo.getArea_board()+"|"+basicInfo.getTrade_board(), Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row3, 16, "当前股价", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row3, 17, basicInfo.getPrice(), Font.COLOR_RED);


        Map<String, BonusFinancing> bfMap = eastMoneySpider.getBonusFinancing(code).stream().collect(Collectors.toMap(BonusFinancing::getSj, bf -> bf, (oldBf, newBf) -> newBf));
        benefitList.stream().filter(xlsData -> StringUtils.equals(xlsData.getKey(), "科目\\时间")).forEach(xlsData -> {
            HSSFRow row4 = sheet.createRow(4);
            HSSFUtil.rowColumn(workbook, row4, 0, "分红明细", Font.COLOR_NORMAL);
            if (0 != xlsData.getYData1()) {
                HSSFUtil.rowColumn(workbook, row4, 1, bfMap.get(new Double(xlsData.getYData1()).intValue() + "") == null ? "0" : bfMap.get(new Double(xlsData.getYData1()).intValue() + "").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData2()) {
                HSSFUtil.rowColumn(workbook, row4, 2, bfMap.get(new Double(xlsData.getYData2()).intValue() + "") == null ? "0" : bfMap.get(new Double(xlsData.getYData2()).intValue() + "").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData3()) {
                HSSFUtil.rowColumn(workbook, row4, 3, bfMap.get(new Double(xlsData.getYData3()).intValue() + "") == null ? "0" : bfMap.get(new Double(xlsData.getYData3()).intValue() + "").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData4()) {
                HSSFUtil.rowColumn(workbook, row4, 4, bfMap.get(new Double(xlsData.getYData4()).intValue() + "") == null ? "0" : bfMap.get(new Double(xlsData.getYData4()).intValue() + "").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData5()) {
                HSSFUtil.rowColumn(workbook, row4, 5, bfMap.get(new Double(xlsData.getYData5()).intValue() + "") == null ? "0" : bfMap.get(new Double(xlsData.getYData5()).intValue() + "").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData6()) {
                HSSFUtil.rowColumn(workbook, row4, 6, bfMap.get(new Double(xlsData.getYData6()).intValue() + "") == null ? "0" : bfMap.get(new Double(xlsData.getYData6()).intValue() + "").getMgsy(), Font.COLOR_NORMAL);
            }


            HSSFUtil.rowColumn(workbook, row4, 13, "估值结果", Font.COLOR_NORMAL);
            HSSFUtil.rowColumn(workbook, row4, 14, "取值", Font.COLOR_NORMAL);
            HSSFUtil.rowColumn(workbook, row4, 15, "PE", Font.COLOR_NORMAL);
            HSSFUtil.rowColumn(workbook, row4, 16, "PB", Font.COLOR_NORMAL);
            HSSFUtil.rowColumn(workbook, row4, 17, "估值股价", Font.COLOR_RED);
        });

        HSSFRow row5 = sheet.createRow(5);
        HSSFUtil.rowColumn(workbook, row5, 0, "分红：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row5, 1, "B5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 2, "C5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 3, "D5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 4, "E5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 5, "F5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 6, "G5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row5, 13, "PE三阶段估值模型", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row5, 14, "取均值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row5, 15, "K30", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 16, "K31", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 17, "K35", Font.COLOR_RED, "0.00");


        HSSFRow row6 = sheet.createRow(6);
        HSSFUtil.rowColumn(workbook, row6, 0, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row6, 1, "B6/B3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 2, "C6/C3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 3, "D6/D3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 4, "E6/E3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 5, "F6/F3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 6, "G6/G3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumn(workbook, row6, 14, "取低值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row6, 15, "K45", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row6, 16, "K46", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row6, 17, "K50", Font.COLOR_RED, "0.00");


        HSSFRow row7 = HSSFUtil.row(workbook, sheet, 7, "加权净资产收益率", "净资产收益率", mainList);
        HSSFUtil.rowColumn(workbook, row7, 13, "PB三阶段估值模型", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row7, 14, "取均值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row7, 15, "O61", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row7, 16, "O62", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row7, 17, "O66", Font.COLOR_RED, "0.00");

        HSSFRow row8 = HSSFUtil.row(workbook, sheet, 8, "净利润同比增长率", "净利润同比增长率", mainList);
        HSSFUtil.rowColumn(workbook, row8, 14, "取低值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row8, 15, "O89", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row8, 16, "O90", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row8, 17, "O94", Font.COLOR_RED, "0.00");

        HSSFRow row9 = HSSFUtil.row(workbook, sheet, 9, "扣非净利润同比增长率", "扣非净利润同比增长率", mainList);
        HSSFUtil.rowColumn(workbook, row9, 13, "自由现金流折现:", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row9, 17, "=自由现金流折现模型!G40", Font.COLOR_RED);



        HSSFRow row11 = sheet.createRow(11);
        HSSFUtil.rowColumn(workbook, row11, 1, "a值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 2, "b值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 3, "2019年模型值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 4, "年化增速", Font.COLOR_NORMAL);

        HSSFRow row12 = sheet.createRow(12);
        HSSFUtil.rowColumn(workbook, row12, 0, "用指数函数回归：过去五年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row12, 1, "INDEX(LOGEST(B3:K3,B$1:K$1),1)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row12, 2, "INDEX(LOGEST(B3:K3,B$1:K$1),2)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row12, 3, "C13*B13^B1", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row12, 4, "B13-1", Font.COLOR_NORMAL, "0.00%");

        HSSFRow row13 = sheet.createRow(13);
        HSSFUtil.rowColumn(workbook, row13, 0, "用指数函数回归：过去十年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row13, 1, "INDEX(LOGEST(B3:F3,B$1:F$1),1)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row13, 2, "INDEX(LOGEST(B3:F3,B$1:F$1),2)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row13, 3, "C14*B14^B1", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row13, 4, "B14-1", Font.COLOR_NORMAL, "0.00%");


        HSSFRow row15 = sheet.createRow(15);
        HSSFUtil.rowColumn(workbook, row15, 0, "公司指标", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row15, 1, "5年数值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row15, 2, "10年数值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row15, 3, "B1", Font.COLOR_NORMAL, "");
        HSSFUtil.rowColumn(workbook, row15, 4, "均值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row15, 5, "低值", Font.COLOR_NORMAL);

        HSSFRow row16 = sheet.createRow(16);
        HSSFUtil.rowColumn(workbook, row16, 0, "归母净利润年化增速", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row16, 1, "E13", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row16, 2, "E14", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row16, 3, "B9/100", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row16, 4, "AVERAGE(B17:C17)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row16, 5, "MIN(B17:C17)", Font.COLOR_NORMAL, "0.0%");

        HSSFRow row17 = sheet.createRow(17);
        HSSFUtil.rowColumn(workbook, row17, 0, "分红率", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row17, 1, "AVERAGE(C7:G7)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row17, 3, "C7", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row17, 4, "AVERAGE(B18:C18)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row17, 5, "MIN(B18:D18)", Font.COLOR_NORMAL, "0.0%");

        HSSFRow row18 = sheet.createRow(18);
        HSSFUtil.rowColumn(workbook, row18, 0, "ROE", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row18, 1, "AVERAGE(B8:F8)/100", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 2, "AVERAGE(B8:K8)/100", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 3, "B8/100", Font.COLOR_RED, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row18, 4, "AVERAGE(B19:C19)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 5, "MIN(B19:D19)", Font.COLOR_NORMAL, "0.0%");

        HSSFRow row19 = sheet.createRow(19);
        HSSFUtil.rowColumn(workbook, row19, 0, "模型利润", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row19, 1, "D13", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row19, 2, "D14", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row19, 3, "B3", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row19, 4, "AVERAGE(B20:D20)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row19, 5, "MIN(B20:D20)", Font.COLOR_NORMAL, "0.0");

        // PE三阶段估值模型（取均值）
        PE3avg(code, workbook, eastMoneySpider, sheet);

        // PE三阶段估值模型（取低值）
        PE3min(code, workbook, eastMoneySpider, sheet);

        //PB三阶段估值模型（取均值）
        PB3avg(code, workbook, eastMoneySpider, sheet);

        //PB三阶段估值模型（取低值）
        PB3min(code, workbook, eastMoneySpider, sheet);
    }

    private static void PE3avg(String code, HSSFWorkbook workbook, EastMoneySpider eastMoneySpider, HSSFSheet sheet) {
        HSSFRow row21 = sheet.createRow(21);
        HSSFUtil.rowColumn(workbook, row21, 0, "1、股东价值-PE三阶段估值模型（取均值）", Font.COLOR_NORMAL);

        HSSFRow row22 = sheet.createRow(22);
        HSSFUtil.rowColumn(workbook, row22, 0, "三阶段模型", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 1, "利润增长率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 2, "归母净利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 3, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 4, "ROE", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 5, "股东价值率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 6, "折现率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 7, "折现系数", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 8, "股东价值折现", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row22, 9, "股东价值折现求和", Font.COLOR_NORMAL);

        HSSFRow row23 = sheet.createRow(23);
        HSSFUtil.rowColumn(workbook, row23, 0, "未来10年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row23, 1, "E17", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 3, "E18", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 4, "E19", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 5, "D24+(1-D24)*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumn(workbook, row23, 6, "10%", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row23, 9, "1-10年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row23, 10, "(1+B24)*F24/(G24-B24)-((1+B24)^11)*F24/(G24-B24)/(1+G24)^10", Font.COLOR_RED, "0.0");

        HSSFRow row24 = sheet.createRow(24);
        HSSFUtil.rowColumn(workbook, row24, 0, "11年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row24, 1, "B24-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 2, "(1+B24)^10*(1+B25)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row24, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 4, "B25/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 5, "D25+(1-D25)*E25", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 7, "1/(1+G25)^11", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 8, "C25*F25*H25", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row24, 9, "10-20年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row24, 10, "SUM(I25:I34)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row25 = sheet.createRow(25);
        HSSFUtil.rowColumn(workbook, row25, 0, "12年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row25, 1, "B25-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 2, "C25*(1+B26)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row25, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 4, "B26/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 5, "D26+(1-D26)*E26", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 7, "H25/(1+G26)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row25, 8, "C26*F26*H26", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row25, 9, "20年后", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row25, 10, "C34*(1+B35)*F35/(G35-B35)*H34", Font.COLOR_NORMAL, "0.00");


        HSSFRow row26 = sheet.createRow(26);
        HSSFUtil.rowColumn(workbook, row26, 0, "13年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row26, 1, "B26-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 2, "C26*(1+B27)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row26, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 4, "B27/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 5, "D27+(1-D27)*E27", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 7, "H26/(1+G27)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row26, 8, "C27*F27*H27", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row26, 9, "利润价值合计", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row26, 10, "SUM(K24:K26)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row27 = sheet.createRow(27);
        HSSFUtil.rowColumn(workbook, row27, 0, "14年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row27, 1, "B27-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 2, "C27*(1+B28)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row27, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 4, "B28/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 5, "D28+(1-D28)*E28", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 7, "H27/(1+G28)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row27, 8, "C28*F28*H28", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row27, 9, "资产清算价值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row27, 10, "1/D19", Font.COLOR_NORMAL, "0.00");


        HSSFRow row28 = sheet.createRow(28);
        HSSFUtil.rowColumn(workbook, row28, 0, "15年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row28, 1, "B28-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 2, "C28*(1+B29)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row28, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 4, "B29/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 5, "D29+(1-D29)*E29", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 7, "H28/(1+G29)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row28, 8, "C29*F29*H29", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row28, 9, "价值合计", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row28, 10, "K27+K28", Font.COLOR_NORMAL, "0.00");


        HSSFRow row29 = sheet.createRow(29);
        HSSFUtil.rowColumn(workbook, row29, 0, "16年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row29, 1, "B29-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row29, 2, "C29*(1+B30)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row29, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row29, 4, "B30/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row29, 5, "D30+(1-D30)*E30", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row29, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row29, 7, "H29/(1+G30)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row29, 8, "C30*F30*H30", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row29, 9, "PE估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row29, 10, "K29", Font.COLOR_NORMAL, "0.0");


        HSSFRow row30 = sheet.createRow(30);
        HSSFUtil.rowColumn(workbook, row30, 0, "17年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row30, 1, "B30-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 2, "C30*(1+B31)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row30, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 4, "B31/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 5, "D31+(1-D31)*E31", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row30, 7, "H30/(1+G31)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row30, 8, "C31*F31*H31", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row30, 9, "PB估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row30, 10, "K30*D19", Font.COLOR_NORMAL, "0.0");


        HSSFRow row31 = sheet.createRow(31);
        HSSFUtil.rowColumn(workbook, row31, 0, "18年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row31, 1, "B31-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 2, "C31*(1+B32)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row31, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 4, "B32/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 5, "D32+(1-D32)*E32", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row31, 7, "H31/(1+G32)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row31, 8, "C32*F32*H32", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row31, 9, "合理利润：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row31, 10, "E20", Font.COLOR_NORMAL, "0.00");


        HSSFRow row32 = sheet.createRow(32);
        HSSFUtil.rowColumn(workbook, row32, 0, "19年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row32, 1, "B32-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 2, "C32*(1+B33)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row32, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 4, "B33/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 5, "D33+(1-D33)*E33", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row32, 7, "H32/(1+G33)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row32, 8, "C33*F33*H33", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row32, 9, "合理市值：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row32, 10, "K29*K32", Font.COLOR_NORMAL, "0.00");


        HSSFRow row33 = sheet.createRow(33);
        HSSFUtil.rowColumn(workbook, row33, 0, "20年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row33, 1, "B33-(B24-B35)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 2, "C33*(1+B34)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row33, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 4, "B34/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 5, "D34+(1-D34)*E34", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 6, "G24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row33, 7, "H33/(1+G34)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row33, 8, "C34*F34*H34", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row33, 9, "当前总股本：亿", Font.COLOR_NORMAL);
        Double zgb = eastMoneySpider.getCapitalStockStructure(code);
        HSSFUtil.rowColumn(workbook, row33, 10, zgb + "", Font.COLOR_NORMAL);

        HSSFRow row34 = sheet.createRow(34);
        HSSFUtil.rowColumn(workbook, row34, 0, "20年后", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row34, 1, "2%", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row34, 3, "D24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row34, 4, "B35/B24*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row34, 5, "D35+(1-D35)*E35", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row34, 6, "G24", Font.COLOR_NORMAL, "0.0%");


        HSSFUtil.rowColumn(workbook, row34, 9, "合理股价", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row34, 10, "K33/K34", Font.COLOR_NORMAL, "0.00");
    }

    private static void PE3min(String code, HSSFWorkbook workbook, EastMoneySpider eastMoneySpider, HSSFSheet sheet) {
        HSSFRow row36 = sheet.createRow(36);
        HSSFUtil.rowColumn(workbook, row36, 0, "2、股东价值-PE三阶段估值模型（取低值）", Font.COLOR_NORMAL);

        HSSFRow row37 = sheet.createRow(37);
        HSSFUtil.rowColumn(workbook, row37, 0, "三阶段模型", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 1, "利润增长率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 2, "归母净利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 3, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 4, "ROE", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 5, "股东价值率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 6, "折现率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 7, "折现系数", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 8, "股东价值折现", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row37, 9, "股东价值折现求和", Font.COLOR_NORMAL);

        HSSFRow row38 = sheet.createRow(38);
        HSSFUtil.rowColumn(workbook, row38, 0, "未来10年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row38, 1, "F17", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row38, 3, "F18", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row38, 4, "F19", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row38, 5, "D39+(1-D39)*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumn(workbook, row38, 6, "10%", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row38, 9, "1-10年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row38, 10, "(1+B39)*F39/(G39-B39)-((1+B39)^11)*F39/(G39-B39)/(1+G39)^10", Font.COLOR_RED, "0.0");

        HSSFRow row39 = sheet.createRow(39);
        HSSFUtil.rowColumn(workbook, row39, 0, "11年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row39, 1, "B39-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 2, "(1+B39)^10*(1+B40)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row39, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 4, "B40/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 5, "D40+(1-D40)*E40", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row39, 7, "1/(1+G40)^11", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row39, 8, "C40*F40*H40", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row39, 9, "10-20年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row39, 10, "SUM(I40:I49)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row40 = sheet.createRow(40);
        HSSFUtil.rowColumn(workbook, row40, 0, "12年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row40, 1, "B40-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row40, 2, "C40*(1+B41)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row40, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row40, 4, "B41/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row40, 5, "D41+(1-D41)*E41", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row40, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row40, 7, "H40/(1+G41)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row40, 8, "C41*F41*H41", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row40, 9, "20年后", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row40, 10, "C49*(1+B50)*F50/(G50-B50)*H49", Font.COLOR_NORMAL, "0.00");


        HSSFRow row41 = sheet.createRow(41);
        HSSFUtil.rowColumn(workbook, row41, 0, "13年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row41, 1, "B41-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 2, "C41*(1+B42)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row41, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 4, "B42/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 5, "D42+(1-D42)*E42", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row41, 7, "H41/(1+G42)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row41, 8, "C42*F42*H42", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row41, 9, "利润价值合计", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row41, 10, "SUM(K39:K41)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row42 = sheet.createRow(42);
        HSSFUtil.rowColumn(workbook, row42, 0, "14年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row42, 1, "B42-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row42, 2, "C42*(1+B43)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row42, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row42, 4, "B43/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row42, 5, "D43+(1-D43)*E43", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row42, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row42, 7, "H42/(1+G43)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row42, 8, "C43*F43*H43", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row42, 9, "资产清算价值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row42, 10, "1/D19", Font.COLOR_NORMAL, "0.00");


        HSSFRow row43 = sheet.createRow(43);
        HSSFUtil.rowColumn(workbook, row43, 0, "15年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row43, 1, "B43-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row43, 2, "C43*(1+B44)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row43, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row43, 4, "B44/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row43, 5, "D44+(1-D44)*E44", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row43, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row43, 7, "H43/(1+G44)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row43, 8, "C44*F44*H44", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row43, 9, "价值合计", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row43, 10, "K42+K43", Font.COLOR_NORMAL, "0.00");


        HSSFRow row44 = sheet.createRow(44);
        HSSFUtil.rowColumn(workbook, row44, 0, "16年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row44, 1, "B44-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row44, 2, "C44*(1+B45)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row44, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row44, 4, "B45/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row44, 5, "D45+(1-D45)*E45", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row44, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row44, 7, "H44/(1+G45)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row44, 8, "C45*F45*H45", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row44, 9, "PE估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row44, 10, "K44", Font.COLOR_NORMAL, "0.0");


        HSSFRow row45 = sheet.createRow(45);
        HSSFUtil.rowColumn(workbook, row45, 0, "17年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row45, 1, "B45-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row45, 2, "C45*(1+B46)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row45, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row45, 4, "B46/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row45, 5, "D46+(1-D46)*E46", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row45, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row45, 7, "H45/(1+G46)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row45, 8, "C46*F46*H46", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row45, 9, "PB估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row45, 10, "K45*D19", Font.COLOR_NORMAL, "0.0");


        HSSFRow row46 = sheet.createRow(46);
        HSSFUtil.rowColumn(workbook, row46, 0, "18年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row46, 1, "B46-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row46, 2, "C46*(1+B47)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row46, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row46, 4, "B47/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row46, 5, "D47+(1-D47)*E47", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row46, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row46, 7, "H46/(1+G47)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row46, 8, "C47*F47*H47", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row46, 9, "合理利润：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row46, 10, "F20", Font.COLOR_NORMAL, "0.00");


        HSSFRow row47 = sheet.createRow(47);
        HSSFUtil.rowColumn(workbook, row47, 0, "19年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row47, 1, "B47-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row47, 2, "C47*(1+B48)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row47, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row47, 4, "B48/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row47, 5, "D48+(1-D48)*E48", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row47, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row47, 7, "H47/(1+G48)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row47, 8, "C48*F48*H48", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row47, 9, "合理市值：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row47, 10, "K44*K47", Font.COLOR_NORMAL, "0.00");


        HSSFRow row48 = sheet.createRow(48);
        HSSFUtil.rowColumn(workbook, row48, 0, "20年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row48, 1, "B48-(B39-B50)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row48, 2, "C48*(1+B49)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row48, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row48, 4, "B49/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row48, 5, "D49+(1-D49)*E49", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row48, 6, "G39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row48, 7, "H48/(1+G49)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row48, 8, "C49*F49*H49", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row48, 9, "当前总股本：亿", Font.COLOR_NORMAL);
        Double zgb = eastMoneySpider.getCapitalStockStructure(code);
        HSSFUtil.rowColumn(workbook, row48, 10, zgb + "", Font.COLOR_NORMAL);

        HSSFRow row49 = sheet.createRow(49);
        HSSFUtil.rowColumn(workbook, row49, 0, "20年后", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row49, 1, "2%", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row49, 3, "D39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row49, 4, "B50/B39*E39", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row49, 5, "D50+(1-D50)*E50", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row49, 6, "G39", Font.COLOR_NORMAL, "0.0%");


        HSSFUtil.rowColumn(workbook, row49, 9, "合理股价", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row49, 10, "K48/K49", Font.COLOR_NORMAL, "0.00");
    }


    private static void PB3avg(String code, HSSFWorkbook workbook, EastMoneySpider eastMoneySpider, HSSFSheet sheet) {
        HSSFRow row51 = sheet.createRow(51);
        HSSFUtil.rowColumn(workbook, row51, 0, "3、股东价值-PB三阶段估值模型（取均值）", Font.COLOR_NORMAL);

        HSSFRow row52 = sheet.createRow(52);
        HSSFUtil.rowColumn(workbook, row52, 0, "计算模型", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 1, "净资产", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 2, "ROE", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 3, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 4, "净资产增速", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 5, "归母净利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 6, "净利润增速", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 7, "股东价值率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 8, "股东价值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 9, "折现率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 10, "折现系数", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 11, "股东价值折现", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 12, "股东价值折现：累计求和", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row52, 13, "PB估值 PE估值", Font.COLOR_NORMAL);

        HSSFRow row53 = sheet.createRow(53);
        HSSFUtil.rowColumn(workbook, row53, 0, "今年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row53, 1, "1", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row53, 2, "B8/100", Font.COLOR_NORMAL, "0.0%");
        //HSSFUtil.rowColumn(workbook, row17, 9, "10%", Font.COLOR_RED);
        //rowColumn(workbook, row17, 9, "1-10年", Font.COLOR_NORMAL);
        //rowColumnFormula(workbook, row17, 10, "(1+B18)*F18/(G18-B18)-((1+B18)^11)*F18/(G18-B18)/(1+G18)^10", Font.COLOR_RED, "0.0");


        HSSFRow row54 = sheet.createRow(54);
        HSSFUtil.rowColumn(workbook, row54, 0, "未来10年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row54, 2, "E19", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row54, 3, "E18", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row54, 7, "D55+(1-D55)*C55", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumn(workbook, row54, 9, "10%", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row54, 13, "1-10年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row54, 14, "SUM(L55:L65)", Font.COLOR_RED, "0.00");

        HSSFRow row55 = sheet.createRow(55);
        HSSFUtil.rowColumn(workbook, row55, 0, "第1年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row55, 1, "B54*(1+E56)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row55, 2, "C$55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row55, 3, "D$55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row55, 4, "C56*(1-D56)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row55, 5, "B56*C56", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row55, 6, "E56", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row55, 7, "D56+(1-D56)*C56", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row55, 8, "F56*H56", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row55, 9, "J55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row55, 10, "1/(1+J56)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row55, 11, "I56*K56", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row55, 12, "SUM(L$56:L56)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row55, 13, "11-20年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row55, 14, "SUM(L67:L76)", Font.COLOR_RED, "0.00");

        HSSFRow row56 = getCells(workbook, sheet, 56, 2, 56);
        HSSFUtil.rowColumn(workbook, row56, 13, "20年后", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row56, 14, "I78/(J78-E78)*K76", Font.COLOR_RED, "0.00");

        HSSFRow row57 = getCells(workbook, sheet, 57, 3, 56);
        HSSFUtil.rowColumn(workbook, row57, 13, "利润价值合计", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row57, 14, "O55+O56+O57", Font.COLOR_RED, "0.00");

        HSSFRow row58 = getCells(workbook, sheet, 58, 4, 56);
        HSSFUtil.rowColumn(workbook, row58, 13, "资产清算价值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row58, 14, "1", Font.COLOR_RED);

        HSSFRow row59 = getCells(workbook, sheet, 59, 5, 56);
        HSSFUtil.rowColumn(workbook, row59, 13, "价值合计", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row59, 14, "O58+O59", Font.COLOR_RED, "0.00");

        HSSFRow row60 = getCells(workbook, sheet, 60, 6, 56);
        HSSFUtil.rowColumn(workbook, row60, 13, "PB估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row60, 14, "O60", Font.COLOR_RED, "0.00");

        HSSFRow row61 = getCells(workbook, sheet, 61, 7, 56);
        HSSFUtil.rowColumn(workbook, row61, 13, "PE估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row61, 14, "O61/C54", Font.COLOR_RED, "0.00");

        HSSFRow row62 = getCells(workbook, sheet, 62, 8, 56);
        HSSFUtil.rowColumn(workbook, row62, 13, "合理利润：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row62, 14, "E20", Font.COLOR_NORMAL, "0.00");

        HSSFRow row63 = getCells(workbook, sheet, 63, 9, 56);
        HSSFUtil.rowColumn(workbook, row63, 13, "合理市值：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row63, 14, "O62*O63", Font.COLOR_NORMAL, "0.00");

        HSSFRow row64 = getCells(workbook, sheet, 64, 10, 56);
        HSSFUtil.rowColumn(workbook, row64, 13, "当前总股本：亿", Font.COLOR_NORMAL);
        Double zgb = eastMoneySpider.getCapitalStockStructure(code);
        HSSFUtil.rowColumn(workbook, row64, 14, zgb + "", Font.COLOR_NORMAL);


        HSSFRow row65 = sheet.createRow(65);
        HSSFUtil.rowColumn(workbook, row65, 0, "11-20年", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row65, 13, "合理股价", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row65, 14, "O64/O65", Font.COLOR_NORMAL, "0.00");


        HSSFRow row66 = sheet.createRow(66);
        HSSFUtil.rowColumn(workbook, row66, 0, "第11年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row66, 1, "B65*(1+E67)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row66, 2, "C55-(C55-C77)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row66, 3, "D$55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row66, 4, "C67*(1-D67)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row66, 5, "B67*C67", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row66, 6, "F67/F65-1", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row66, 7, "D67+(1-D67)*C67", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row66, 8, "F67*H67", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row66, 9, "J55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row66, 10, "K65/(1+J67)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row66, 11, "I67*K67", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row66, 12, "SUM(L$56:L67)", Font.COLOR_NORMAL, "0.00");

        getCell2(workbook, sheet, 67, 12, 56);
        getCell2(workbook, sheet, 68, 13, 56);
        getCell2(workbook, sheet, 69, 14, 56);
        getCell2(workbook, sheet, 70, 15, 56);
        getCell2(workbook, sheet, 71, 16, 56);
        getCell2(workbook, sheet, 72, 17, 56);
        getCell2(workbook, sheet, 73, 18, 56);
        getCell2(workbook, sheet, 74, 19, 56);
        getCell2(workbook, sheet, 75, 20, 56);

        HSSFRow row76 = sheet.createRow(76);
        HSSFUtil.rowColumn(workbook, row76, 0, "20年后", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row76, 2, "10%", Font.COLOR_RED);

        HSSFRow row77 = sheet.createRow(77);
        HSSFUtil.rowColumn(workbook, row77, 0, "第21年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row77, 1, "B76*(1+E78)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row77, 2, "C77", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row77, 3, "D$55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row77, 4, "C78*(1-D78)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row77, 5, "B78*C78", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row77, 6, "F78/F76-1", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row77, 7, "D78+(1-D78)*C78", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row77, 8, "F78*H78", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row77, 9, "J55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row77, 10, "K76/(1+J78)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row77, 11, "I78*K78", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row77, 12, "SUM(L$56:L78)", Font.COLOR_NORMAL, "0.00");
    }

    private static void getCell2(HSSFWorkbook workbook, HSSFSheet sheet, int row, int year, int fixedRow) {
        HSSFRow rowNum = sheet.createRow(row);
        HSSFUtil.rowColumn(workbook, rowNum, 0, "第" + year + "年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, rowNum, 1, "B" + row + "*(1+E" + (row + 1) + ")", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 2, "C" + row + "-(C$55-C$77)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 3, "D$55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 4, "C" + (row + 1) + "*(1-D" + (row + 1) + ")", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 5, "B" + (row + 1) + "*C" + (row + 1) + "", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 6, "F" + (row + 1) + "/F" + row + "-1", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 7, "D" + (row + 1) + "+(1-D" + (row + 1) + ")*C" + (row + 1) + "", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 8, "F" + (row + 1) + "*H" + (row + 1) + "", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 9, "J55", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 10, "K" + row + "/(1+J" + (row + 1) + ")", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 11, "I" + (row + 1) + "*K" + (row + 1) + "", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 12, "SUM(L$" + fixedRow + ":L" + (row + 1) + ")", Font.COLOR_NORMAL, "0.00");
    }

    private static HSSFRow getCells(HSSFWorkbook workbook, HSSFSheet sheet, int row, int year, int fixedRow) {
        HSSFRow rowNum = sheet.createRow(row);
        HSSFUtil.rowColumn(workbook, rowNum, 0, "第" + year + "年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, rowNum, 1, "B" + row + "*(1+E" + (row + 1) + ")", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 2, "C" + (row - 1), Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 3, "D" + (row - 1), Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 4, "C" + (row + 1) + "*(1-D" + (row + 1) + ")", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 5, "B" + (row + 1) + "*C" + (row + 1) + "", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 6, "E" + (row + 1) + "", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 7, "D" + (row + 1) + "+(1-D" + (row + 1) + ")*C" + (row + 1) + "", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 8, "F" + (row + 1) + "*H" + (row + 1) + "", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 9, "J" + (row - 1), Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 10, "K" + row + "/(1+J" + (row + 1) + ")", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 11, "I" + (row + 1) + "*K" + (row + 1) + "", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, rowNum, 12, "SUM(L$" + fixedRow + ":L" + (row + 1) + ")", Font.COLOR_NORMAL, "0.00");
        return rowNum;
    }


    private static void PB3min(String code, HSSFWorkbook workbook, EastMoneySpider eastMoneySpider, HSSFSheet sheet) {
        HSSFRow row79 = sheet.createRow(79);
        HSSFUtil.rowColumn(workbook, row79, 0, "4、股东价值-PB三阶段估值模型（取低值）", Font.COLOR_NORMAL);

        HSSFRow row80 = sheet.createRow(80);
        HSSFUtil.rowColumn(workbook, row80, 0, "计算模型", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 1, "净资产", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 2, "ROE", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 3, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 4, "净资产增速", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 5, "归母净利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 6, "净利润增速", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 7, "股东价值率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 8, "股东价值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 9, "折现率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 10, "折现系数", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 11, "股东价值折现", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 12, "股东价值折现：累计求和", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row80, 13, "PB估值 PE估值", Font.COLOR_NORMAL);

        HSSFRow row81 = sheet.createRow(81);
        HSSFUtil.rowColumn(workbook, row81, 0, "今年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row81, 1, "1", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row81, 2, "B8/100", Font.COLOR_NORMAL, "0.0%");
        //HSSFUtil.rowColumn(workbook, row17, 9, "10%", Font.COLOR_RED);
        //rowColumn(workbook, row17, 9, "1-10年", Font.COLOR_NORMAL);
        //rowColumnFormula(workbook, row17, 10, "(1+B18)*F18/(G18-B18)-((1+B18)^11)*F18/(G18-B18)/(1+G18)^10", Font.COLOR_RED, "0.0");


        HSSFRow row82 = sheet.createRow(82);
        HSSFUtil.rowColumn(workbook, row82, 0, "未来10年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row82, 2, "F19", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row82, 3, "F18", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row82, 7, "D83+(1-D83)*C83", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumn(workbook, row82, 9, "10%", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row82, 13, "1-10年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row82, 14, "SUM(L83:L93)", Font.COLOR_RED, "0.00");

        HSSFRow row83 = sheet.createRow(83);
        HSSFUtil.rowColumn(workbook, row83, 0, "第1年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row83, 1, "B82*(1+E84)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row83, 2, "C$83", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row83, 3, "D$83", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row83, 4, "C84*(1-D84)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row83, 5, "B84*C84", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row83, 6, "E84", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row83, 7, "D84+(1-D84)*C84", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row83, 8, "F84*H84", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row83, 9, "J83", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row83, 10, "1/(1+J84)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row83, 11, "I84*K84", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row83, 12, "SUM(L$84:L84)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row83, 13, "11-20年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row83, 14, "SUM(L95:L104)", Font.COLOR_RED, "0.00");

        HSSFRow row84 = getCells(workbook, sheet, 84, 2, 84);
        HSSFUtil.rowColumn(workbook, row84, 13, "20年后", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row84, 14, "I78/(J106-E106)*K104", Font.COLOR_RED, "0.00");

        HSSFRow row85 = getCells(workbook, sheet, 85, 3, 84);
        HSSFUtil.rowColumn(workbook, row85, 13, "利润价值合计", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row85, 14, "O83+O84+O85", Font.COLOR_RED, "0.00");

        HSSFRow row86 = getCells(workbook, sheet, 86, 4, 84);
        HSSFUtil.rowColumn(workbook, row86, 13, "资产清算价值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row86, 14, "1", Font.COLOR_RED);

        HSSFRow row87 = getCells(workbook, sheet, 87, 5, 84);
        HSSFUtil.rowColumn(workbook, row87, 13, "价值合计", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row87, 14, "O86+O87", Font.COLOR_RED, "0.00");

        HSSFRow row88 = getCells(workbook, sheet, 88, 6, 84);
        HSSFUtil.rowColumn(workbook, row88, 13, "PB估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row88, 14, "O88", Font.COLOR_RED, "0.00");

        HSSFRow row89 = getCells(workbook, sheet, 89, 7, 84);
        HSSFUtil.rowColumn(workbook, row89, 13, "PE估值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row89, 14, "O89/C82", Font.COLOR_RED, "0.00");

        HSSFRow row90 = getCells(workbook, sheet, 90, 8, 84);
        HSSFUtil.rowColumn(workbook, row90, 13, "合理利润：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row90, 14, "F20", Font.COLOR_NORMAL, "0.00");

        HSSFRow row91 = getCells(workbook, sheet, 91, 9, 84);
        HSSFUtil.rowColumn(workbook, row91, 13, "合理市值：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row91, 14, "O90*O91", Font.COLOR_NORMAL, "0.00");

        HSSFRow row92 = getCells(workbook, sheet, 92, 10, 84);
        HSSFUtil.rowColumn(workbook, row92, 13, "当前总股本：亿", Font.COLOR_NORMAL);
        Double zgb = eastMoneySpider.getCapitalStockStructure(code);
        HSSFUtil.rowColumn(workbook, row92, 14, zgb + "", Font.COLOR_NORMAL);


        HSSFRow row93 = sheet.createRow(93);
        HSSFUtil.rowColumn(workbook, row93, 0, "11-20年", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row93, 13, "合理股价", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row93, 14, "O92/O93", Font.COLOR_NORMAL, "0.00");


        HSSFRow row94 = sheet.createRow(94);
        HSSFUtil.rowColumn(workbook, row94, 0, "第11年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row94, 1, "B93*(1+E95)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row94, 2, "C83-(C83-C105)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row94, 3, "D$83", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row94, 4, "C95*(1-D95)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row94, 5, "B95*C95", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row94, 6, "F95/F93-1", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row94, 7, "D95+(1-D95)*C95", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row94, 8, "F95*H95", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row94, 9, "J83", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row94, 10, "K93/(1+J95)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row94, 11, "I95*K95", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row94, 12, "SUM(L$94:L95)", Font.COLOR_NORMAL, "0.00");

        getCell2(workbook, sheet, 95, 12, 84);
        getCell2(workbook, sheet, 96, 13, 84);
        getCell2(workbook, sheet, 97, 14, 84);
        getCell2(workbook, sheet, 98, 15, 84);
        getCell2(workbook, sheet, 99, 16, 84);
        getCell2(workbook, sheet, 100, 17, 84);
        getCell2(workbook, sheet, 101, 18, 84);
        getCell2(workbook, sheet, 102, 19, 84);
        getCell2(workbook, sheet, 103, 20, 84);

        HSSFRow row104 = sheet.createRow(104);
        HSSFUtil.rowColumn(workbook, row104, 0, "20年后", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row104, 2, "10%", Font.COLOR_RED);

        HSSFRow row105 = sheet.createRow(105);
        HSSFUtil.rowColumn(workbook, row105, 0, "第21年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row105, 1, "B104*(1+E106)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row105, 2, "C105", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row105, 3, "D$83", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row105, 4, "C106*(1-D106)", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row105, 5, "B106*C106", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row105, 6, "F106/F104-1", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row105, 7, "D106+(1-D106)*C106", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row105, 8, "F106*H106", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row105, 9, "J83", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row105, 10, "K104/(1+J106)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row105, 11, "I106*K106", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row105, 12, "SUM(L$84:L106)", Font.COLOR_NORMAL, "0.00");
    }
}
